#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2014-08-22 01:14:23
# @Author  : shitao.tommy (hero007asd@gmail.com)
# @Link    : http://example.org
# @Version : $Id$


from framework.db.mysql import BaseDb
from tornado import gen
from lib.libmd5 import md5
from lib import log


class Repairman(BaseDb):

    @gen.coroutine
    def new(self, man_name, username, password, tel, work_id, school_id):
        resp = ''
        sel_sql = "SELECT Fid FROM tb_user_login WHERE Fschool_id=%s AND Fuser_name=%s"
        ins_login_sql = "INSERT INTO tb_user_login(Fuser_name,Fpwd,Ftype,Fschool_id,Fcreate_time) \
                    VALUES(%s,%s,5,%s,UNIX_TIMESTAMP())"
        ins_sql = "INSERT INTO tb_repairman(Fname,Ftel,Fwork_id,Fschool_id,Flogin_id,Fcreate_time) \
                    SELECT %s,%s,%s,%s,a.Fid,UNIX_TIMESTAMP() FROM tb_user_login a WHERE a.Fschool_id=%s AND a.Fuser_name=%s"
        try:
            sel_cur = yield self.execute(sel_sql, (school_id, username))
            if len(sel_cur) < 1:
                ins_login_cur = yield self.upsert_commit(ins_login_sql, ( username, password, school_id))
                ins_cur = yield self.upsert_commit(ins_sql, ( man_name, tel, work_id, school_id, school_id, username))
                resp = sel_cur
            else:
                resp = '-2'
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)


    @gen.coroutine
    def list(self, school_id, start, offset):
        resp = []
        sel_sql = "SELECT Fid,Fname,Fage,Fhire_date,Favatar,Ftel,Fwork_id,Fcreate_time \
                    FROM tb_repairman WHERE Fschool_id=%s ORDER BY Fid DESC LIMIT %s,%s"
        cnt_sql = "SELECT COUNT(Fid) FROM tb_repairman WHERE Fschool_id=%s"
        try:
            sel_cur = yield self.executejson(sel_sql, (school_id,start,offset))
            cnt_ret = yield self.execute(cnt_sql, (school_id,))
            resp = [cnt_ret[0][0], sel_cur]
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
        raise gen.Return(resp)


    @gen.coroutine
    def edit_info(self, school_id, man_id):
        resp = []
        sel_sql = "SELECT Fid,Fname,Ftel,Fwork_id,Flogin_id FROM tb_repairman WHERE Fid=%s AND Fschool_id=%s"
        sel_login_sql = "SELECT Fuser_name,Fpwd FROM tb_user_login WHERE Fid=%s AND Fschool_id=%s"
        try:
            sel_cur = yield self.executejson(sel_sql, (man_id,school_id))
            print sel_cur
            if len(sel_cur) > 0:
                sel_login_cur = yield self.executejson(sel_login_sql, (sel_cur[0]['Flogin_id'], school_id))
                print sel_login_cur
                sel_login_ret = sel_login_cur[0]
            else: sel_login_ret = []
            resp = [sel_cur[0], sel_login_ret]
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)

        raise gen.Return(resp)

    @gen.coroutine
    def edit(self, man_id, man_name, username, password, tel, work_id, school_id):
        resp = 0
        upd_sql = "UPDATE tb_repairman a INNER JOIN tb_user_login b ON a.Flogin_id = b.Fid \
                    SET a.Fname=%s, a.Ftel=%s, a.Fwork_id=%s, b.Fuser_name=%s WHERE a.Fid=%s AND a.Fschool_id=%s"

        try:
            sel_cur = yield self.upsert_commit(upd_sql, (man_name,tel,work_id,username,man_id,school_id))
            resp = sel_cur
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = 0

        raise gen.Return(resp)


    @gen.coroutine
    def delete(self, room_id, school_id):
        resp = 0
        del_sql = "DELETE FROM tb_repairman WHERE Fid=%s AND Fschool_id=%s"

        try:
            del_ret = yield self.upsert_commit(del_sql, (room_id,school_id))
            resp = del_ret
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = 0

        raise gen.Return(resp)


    @gen.coroutine
    def search(self, man_name,tel,work_id,school_id,start,offset):
        resp = []
        sel_sql = []
        cnt_sql = []
        sel_list = []
        cnt_list = []

        sel_sql.append("SELECT Fid,Fname,Fage,Fhire_date,Favatar,Ftel,Fwork_id,Fcreate_time FROM tb_repairman WHERE Fschool_id=%s ")
        cnt_sql.append("SELECT COUNT(Fid) FROM tb_repairman WHERE Fschool_id=%s")
        sel_list.append(school_id)
        cnt_list.append(school_id)
        if man_name != '':
            sel_sql.append(" AND Fname=%s")
            cnt_sql.append(" AND Fname=%s")
            sel_list.append(man_name)
            cnt_list.append(man_name)
        if tel != '':
            sel_sql.append(" AND Ftel=%s")
            cnt_sql.append(" AND Ftel=%s")
            sel_list.append(tel)
            cnt_list.append(tel)
        if work_id != '':
            sel_sql.append(" AND Fwork_id=%s")
            cnt_sql.append(" AND Fwork_id=%s")
            sel_list.append(work_id)
            cnt_list.append(work_id)
        sel_sql.append(" ORDER BY Fid DESC LIMIT %s,%s")
        sel_list.append(start)
        sel_list.append(offset)
        sel_sql = " ".join(sel_sql)
        cnt_sql = " ".join(cnt_sql)
        sel_tpl = tuple(sel_list)
        cnt_tpl = tuple(cnt_list)
        try:
            sel_cur = yield self.executejson(sel_sql, sel_tpl)
            cnt_ret = yield self.execute(cnt_sql, cnt_tpl)
            resp = [cnt_ret[0][0], sel_cur]
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
        raise gen.Return(resp)